package com.dlplay.flightinfo.db.tools;

import java.io.File;
import java.io.FileWriter;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;

import com.dlplay.flightinfo.source.FlightInfoDsUtility;
import com.dlplay.flightinfo.core.FlightSystem;
import com.dlplay.flightinfo.data.Flight;
import com.dlplay.flightinfo.data.SystemAttribute;
import com.dlplay.flightinfo.db.FlightInfoDbMngr;
import com.dlplay.flightinfo.db.FlightInfoDbUtility;
import com.dlplay.flightinfo.db.business.FlightEntity;
import com.dlplay.flightinfo.db.business.FlightQuery;
import com.dlplay.flightinfo.db.datastore.EMF;
import com.dlplay.flightinfo.test.AbstractGaeTestSuite;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 
 * @author
 */
public class RestoreFromExcel {
	
	protected static Logger log = Logger.getLogger(RestoreFromExcel.class.getName());
	
	final static String EXCEL_DATA_FILE = "./war/WEB-INF/FlightData.xls";
	final static  int TABLE_FIELD_MAXNUM = 8; //actually 24 - 6, for there is a field "equip_name"
	//Indexes in excel
	final static  int FROM_INDEX = 0;
	final static  int TO_INDEX = 1;
	final static  int AIRCOMPANY_INDEX = 2;
	final static  int NUMBER_INDEX = 3;
	final static  int PLANETYPE_INDEX = 4;
	final static  int PLAN_DEPARTURETIME_INDEX = 5;
	final static  int PLAN_ARRIVALTIME_INDEX = 6;
	final static  int SCHEDULE_INDEX = 7;
	final static  int END_OF_INDEX = 8;
	
	final static  String SQL_TABLE_FIELDS = " (equip_name, name, equip_type, part_number, serial_number, owner, ip, location, description, revision, equip_subtype, rma, gdx_version,  mac_address, shelf, group_id, future1, future2, slotid, portid, serverid, status, id, global_id) ";
	final static  String SQL_TABLE_NAME ="equipment";
	final static  String UTILITY_PROPERTIES_FILE ="excelToSql.prop";
	
	final static  int GLOBAL_ID_CAGE_START = 1;
	final static  int ID_CAGE_START = 0;
	final static  int GLOBAL_ID_SFC_START = 1000;//For MSS_EMCP
	final static  int ID_SFC_START = 0;//For MSS_EMCP
	final static  int GLOBAL_ID_LIC_START = 2000; //For MSS_SC
	final static  int ID_LIC_START = 0; //For MSS_SC
	final static  int GLOBAL_ID_SFM_START = 3000;//For MSS_PM
	final static  int GLOBAL_ID_IXIAPORT_START = 4000;
	final static  int GLOBAL_ID_SPORT_START = 5000;

	/**
	 * 
	 * @param file 
	 * @return
	 * @throws Exception
	 */
	public static void read(File file) throws Exception {
		
		StringBuffer sqlStatement = new StringBuffer();
		Date temp = new Date();
		String time = null;
		int index = 0;
		
		log.info("Starting read excel file: " + file.getName());
		log.info("Starting read excel file absolute path: " + file.getAbsolutePath());
		//Write to a txt file
		//Write to properties file
		
		Workbook wb = null;
		EntityManager em = null;
		try 
		{
			//First get the DBM
			
			em = EMF.getEntityManager();
			if (em == null)
			{
				throw(new Exception("No EntityManager!"));
			}

			// read excel
			wb = Workbook.getWorkbook(file);
			if (wb != null) 
			{
				// read sheets in excel
				Sheet[] sheets = wb.getSheets();
				if (sheets != null && sheets.length != 0) 
				{
					// iterate the sheet
					for (int i=0;i<sheets.length;i++) 
					{
						// get row number
						int rows = sheets[i].getRows();
						// iterate the row
						for (int j=0;j<rows;j++) 
						{
							// read the row
							Cell[] cells = sheets[i].getRow(j);
							if (cells != null && cells.length != 0) 
							{
								// there are sth in this row
								//Row: 1.出发地 	2.目的地	3.航空公司	4.航班号	5.机型	6.起飞 7.到达	8.班期
								Flight fEntry = new Flight(cells[NUMBER_INDEX].getContents());
								
								fEntry.setFrom(FlightInfoDsUtility.translateLocChToCode(cells[FROM_INDEX].getContents()));
								fEntry.setTo(FlightInfoDsUtility.translateLocChToCode(cells[TO_INDEX].getContents()));
								fEntry.setAirCompany(cells[AIRCOMPANY_INDEX].getContents());
								fEntry.setPlaneType(cells[PLANETYPE_INDEX].getContents());
								
								//15:50
								time = cells[PLAN_ARRIVALTIME_INDEX].getContents();
								index = time.indexOf(":");
								if (index > 0)
								{
									temp.setHours(new Integer(time.substring(0,index)));
									temp.setMinutes(new Integer(time.substring(index+1)));
	
									fEntry.setPlanArrival(temp);
								}
								
								time = cells[PLAN_DEPARTURETIME_INDEX].getContents();
								index = time.indexOf(":");
								if (index > 0)
								{
									temp.setHours(new Integer(time.substring(0,index)));
									temp.setMinutes(new Integer(time.substring(index+1)));
	
									fEntry.setPlanDeparture(temp);
								}
								
								fEntry.setSchedule(cells[SCHEDULE_INDEX].getContents());
								
								try
								{
									// Consider storing the persistent object here
									
									FlightEntity fe = new FlightEntity(em);
									fe.setInstance(fEntry);
									fe.persist();

								} catch (Exception e) {
									// TODO Auto-generated catch block
									log.log(Level.WARNING, "Something wroing + Entry: " + fEntry);
									e.printStackTrace();
								}
							}
						}//Rows
					}//Sheets
				}
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			wb.close();
			em.close();
		}
	}
	
	
	public static void StartRestore() {
		File file = new File(EXCEL_DATA_FILE);
		log.info(file.getParentFile().getAbsolutePath());
		try {
			log.info("haha");
			read(file);
			log.info("haha");
		} catch (Exception e) {
			log.info(e.getMessage());
			e.printStackTrace();
		}

	}
	
	public static void StartRestore(String filename) {
		File file = new File(filename);
		log.info(file.getParentFile().getAbsolutePath());
		try {
			log.info("haha");
			read(file);
			log.info("haha");
		} catch (Exception e) {
			log.info(e.getMessage());
			e.printStackTrace();
		}

	}
	
	public static void CheckIfToRestore() {
		SystemAttribute loadExcel = FlightSystem.getFlightInfoDbManager().loadSysAttribute(FlightInfoDbUtility.SYSTERM_ATTRIBUTE_DB_LOADEXCEL);
		
		if (loadExcel != null)
		{
			//Excel files are already loaded!
			return;
		}
		
		//Now we need to Load the Excel
		StartRestore(FlightInfoDbUtility.SYSTERM_ATTRIBUTE_DB_LOADEXCEL_VALUE);
		
		//What about when we failed to restore? ? ?
		
		//Need to add the attributes to DB
		loadExcel = new SystemAttribute(FlightInfoDbUtility.SYSTERM_ATTRIBUTE_DB_LOADEXCEL,
				FlightInfoDbUtility.SYSTERM_ATTRIBUTE_DB_LOADEXCEL_VALUE);
		
		FlightSystem.getFlightInfoDbManager().storeSysAttribute(loadExcel);

	}
	
	
}